package cn.dao;

import cn.pojo.Admin;
import cn.pojo.BigClass;
import cn.pojo.Customer;
import cn.util.DaoCount;
import cn.util.DaoUtil;
import cn.util.DateUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author jia
 * @Description
 * @date 2019-03-21-11:00
 */
public class CustomerDao {
    /**
     * 添加一个会员
     */
    public void add(Customer customer) {
        String sql = "INSERT INTO customer(cusName, cusLoginName, cusPassword, cusEmail, cusSex, cusPhoto, cusHobby, cusCode, cusBirthday) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection connection = DaoUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, customer.getCusName());
            ps.setString(2, customer.getCusLoginName());
            ps.setString(3, customer.getCusPassword());
            ps.setString(4, customer.getCusEmail());
            ps.setString(5, customer.getCusSex());
            ps.setString(6, customer.getCusPhoto());
            ps.setString(7, customer.getCusHobby());
            ps.setString(8, customer.getCusCode());
            ps.setDate(9, DateUtil.d2t(customer.getCusBirthday()));
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改会员
     */
    public void update(Customer customer) {
        String sql = "update customer set cusName = ?, cusLoginName = ?, cusPassword = ?, cusEmail = ?, cusSex = ?, cusPhoto = ?, cusHobby = ?, cusCode = ?, cusBirthday = ? where id = ?";
        try (Connection connection = DaoUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, customer.getCusName());
            ps.setString(2, customer.getCusLoginName());
            ps.setString(3, customer.getCusPassword());
            ps.setString(4, customer.getCusEmail());
            ps.setString(5, customer.getCusSex());
            ps.setString(6, customer.getCusPhoto());
            ps.setString(7, customer.getCusHobby());
            ps.setString(8, customer.getCusCode());
            ps.setDate(8, DateUtil.d2t(customer.getCusBirthday()));
            ps.setInt(1, customer.getId());
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据用户名和密码
     * 查询一个会员
     */
    public Customer get(String name, String password) {
        Customer customer = null;
        String sql = "select * from customer where binary cusLoginName = ? and binary cusPassword = ?";
        try (Connection connection = DaoUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setString(1, name);
            ps.setString(2, password);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                customer = new Customer();
                customer.setId(rs.getInt("id"));
                customer.setCusName(rs.getString("cusName"));
                customer.setCusLoginName(rs.getString("cusLoginName"));
                customer.setCusPassword(rs.getString("cusPassword"));
                customer.setCusEmail(rs.getString("cusEmail"));
                customer.setCusSex(rs.getString("cusSex"));
                customer.setCusPhoto(rs.getString("cusPhoto"));
                customer.setCusHobby(rs.getString("cusHobby"));
                customer.setCusCode(rs.getString("cusCode"));
                customer.setCusBirthday(rs.getDate("cusBirthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customer;
    }
    /**
     * 查询所有会员
     */
    public List<Customer> list() {
        return list(0, 12);
    }

    /**
     * 分页查询
     */
    public List<Customer> list(int start, int count) {
        List<Customer>  customerList = new ArrayList<>();
        String sql = "select * from customer order by id desc limit ?,?";
        try (Connection connection = DaoUtil.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setInt(1, start);
            ps.setInt(2, count);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Customer customer = new Customer();
                customer.setId(rs.getInt("id"));
                customer.setCusName(rs.getString("cusName"));
                customer.setCusLoginName(rs.getString("cusLoginName"));
                customer.setCusPassword(rs.getString("cusPassword"));
                customer.setCusEmail(rs.getString("cusEmail"));
                customer.setCusSex(rs.getString("cusSex"));
                customer.setCusPhoto(rs.getString("cusPhoto"));
                customer.setCusHobby(rs.getString("cusHobby"));
                customer.setCusCode(rs.getString("cusCode"));
                customer.setCusBirthday(rs.getDate("cusBirthday"));
                customerList.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return customerList;
    }
    /**
     * 查询总记录数
     */
    public int getTotal() {
        String sql = "select count(id) from customer";
        return DaoCount.count(sql);
    }

    public static void main(String[] args) {
        System.out.println(new CustomerDao().list());
    }
}
